
Imports System.Configuration
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data
Imports System.IO
Imports system.Web.UI

Public Class SQLPersistence
    Implements IDisposable

    Private cn
    Private Transa
    Public erro As Boolean = False
    Public DescError As String
    Private motor As String
    Private ParamNombre()
    Private ParamTipo()
    Private ParamValor()
    Private _Comited As Boolean = True

    Private MyProvider As Provider

    Public _NegTransaccion As String

    Public StoreProcedure As String
    Dim Parametro()

    Public DG_NumPaginas As Integer
    Public Dg_NumRegistros As Integer


    Sub DG_SetPaginas(ByRef rp As DotNetResponse.SQLPersistence, ByVal csqlCount As String, ByVal DG_nroRegistros As Integer)
        ExecuteScalar(rp, csqlCount)
        If rp.TieneDatos Then
            'Calcular las paginas
            DG_NumPaginas = rp.Get_Value / DG_nroRegistros
            Dg_NumRegistros = rp.Get_Value
        End If
    End Sub
    Sub DG_SetPaginas(ByRef rp As DotNetResponse.SQLPersistence, ByVal DG_nroRegistros As Integer)
        ExecuteScalar(rp)
        If rp.TieneDatos Then
            'Calcular las paginas
            DG_NumPaginas = rp.Get_Value / DG_nroRegistros
            Dg_NumRegistros = rp.Get_Value
        End If
    End Sub
    Sub AddParametro(ByVal Valor)
        If Parametro Is Nothing Then
            ReDim Parametro(0)
            Parametro(0) = Valor
        Else
            ReDim Preserve Parametro(Parametro.Length)
            Parametro(Parametro.Length - 1) = Valor
        End If
    End Sub


    Public Function Conectar()
        Try
            cn.Open()
            Transa = cn.BeginTransaction()
        Catch ex As Exception
            erro = True
        End Try

    End Function

    Public Function ConectarReadUncommitted()
        Try
            cn.Open()
            Transa = cn.BeginTransaction(IsolationLevel.ReadUncommitted)
        Catch ex As Exception
            erro = True
           
        End Try

    End Function

    Public Function ConectarReadcommitted()
        Try
            cn.Open()
            Transa = cn.BeginTransaction()
            Me._NegTransaccion = Now.ToString("ddMMyyyy hhmmssmmmm")

        Catch ex As Exception
            erro = True
        End Try

    End Function
    Public Enum Provider
        SQLOLEDB = 0
        SQLCLIENT = 1
        ORACLIENT = 2
    End Enum
    'Sub New(ByVal Comited As Boolean, ByVal connectionString As String)
    '    MyProvider = Provider.SQLCLIENT
    '    cn = New SqlConnection
    '    If Not cn Is Nothing Then
    '        If cn.State <> ConnectionState.Closed Then
    '            Exit Sub
    '        End If
    '    End If
    '    cn.ConnectionString = connectionString
    '    _Comited = Comited
    '    If Comited Then
    '        ConectarReadcommitted()
    '    Else
    '        ConectarReadUncommitted()
    '    End If
    'End Sub

  

    Sub New(ByVal Comited As Boolean, ByVal Proveedor As Provider, ByVal ConexionString As String)
        MyProvider = Proveedor

        If MyProvider = Provider.SQLCLIENT Then
            cn = New SqlConnection
        End If

        If MyProvider = Provider.SQLOLEDB Then
            cn = New OleDbConnection
        End If

        If Not cn Is Nothing Then
            If cn.State <> ConnectionState.Closed Then
                Exit Sub
            End If
        End If

        cn.ConnectionString = ConexionString
        _Comited = Comited
        If Comited Then
            ConectarReadcommitted()
        Else
            ConectarReadUncommitted()
        End If

    End Sub


    Sub New(ByVal Comited As Boolean, ByVal ConfigCnn As String, Optional ByVal Proveedor As Provider = Provider.SQLCLIENT)
        MyProvider = Proveedor

        If MyProvider = Provider.SQLCLIENT Then
            cn = New SqlConnection
        End If

        If MyProvider = Provider.SQLOLEDB Then
            cn = New OleDbConnection
        End If

        Dim MyAppSettingsReader As New AppSettingsReader
        ConfigCnn = ConfigurationManager.AppSettings(ConfigCnn).Trim

        If Not cn Is Nothing Then
            If cn.State <> ConnectionState.Closed Then
                Exit Sub
            End If
        End If

        cn.ConnectionString = ConfigCnn
        _Comited = Comited
        If Comited Then
            ConectarReadcommitted()
        Else
            ConectarReadUncommitted()
        End If

    End Sub

    Public Function ExecuteDatatable(ByRef Rp As DotNetResponse.SQLPersistence, ByVal Dg_NroRegistros As Integer, ByVal NroPagina As Integer)
        Try

            Dim dt As New DataTable
            dt.TableName = "nodos"
            Dim dr As SqlClient.SqlDataReader

            Dim Row As DataRow
            Dim cell As New DataColumn
            'If MyProvider = Provider.SQLOLEDB Then
            '    dr = OledbHelper.ExecuteReader(DirectCast(Transa, OleDbTransaction), Rp.StoredProcedure, False, Rp.Parametro)
            'End If
            If MyProvider = Provider.SQLCLIENT Then

                dr = SqlClientHelper.ExecuteReader(DirectCast(Transa, SqlTransaction), Rp.StoredProcedure)
            End If

            Dim i As Integer
            Dim y As Integer = Dg_NroRegistros * NroPagina
            Dim x As Integer = (y - Dg_NroRegistros) + 1

            For i = 0 To dr.FieldCount - 1
                dt.Columns.Add(dr.GetName(i))
            Next
            Dim ct As Integer = 1
            Do While dr.Read
                If ct >= x And ct <= y Then
                    'Lleno mi datatable
                    Row = dt.NewRow
                    For i = 0 To dr.FieldCount - 1
                        Row(i) = dr.GetValue(i)
                    Next
                    Rp.TieneDatos = True
                    dt.Rows.Add(Row)
                End If
                ct = ct + 1
            Loop

            Rp.DtTable = dt
        Catch ex As Exception
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message             
            Catch exinterno As Exception

            End Try


        End Try
    End Function

    'Public Function Executedataset(ByRef Rp As DotNetResponse.SQLPersistence, ByVal Csql As String, ByRef Obj() As Object)

    '    Dim dr
    '    Try
    '        Dim paginacion As Integer = Obj(Obj.Length - 1)
    '        Dim Pagina As Integer = Obj(Obj.Length - 2)
    '        Dim dt As New DataTable
    '        dt.TableName = "nodos"

    '        Dim Row As DataRow
    '        Dim cell As New DataColumn
    '        If MyProvider = Provider.SQLOLEDB Then
    '            dr = OledbHelper.ExecuteReader(DirectCast(Transa, OleDbTransaction), CommandType.Text, Csql)
    '        End If
    '        If MyProvider = Provider.SQLCLIENT Then
    '            dr = SqlClientHelper.ExecuteReader(DirectCast(Transa, SqlTransaction), CommandType.Text, Csql)
    '        End If

    '        Dim i As Integer
    '        For i = 0 To dr.FieldCount - 1
    '            dt.Columns.Add(dr.GetName(i))
    '        Next
    '        Dim ct As Integer = 1, pag As Integer = 0
    '        Do While dr.Read
    '            pag = pag + 1
    '            If ((paginacion * Pagina) - paginacion) + 1 <= pag And (paginacion * Pagina) >= pag Then
    '                Row = dt.NewRow
    '                For i = 0 To dr.FieldCount - 1
    '                    Row(i) = dr.GetValue(i)
    '                Next
    '                Rp.TieneDatos = True
    '                dt.Rows.Add(Row)
    '            End If
    '        Loop
    '        Rp.Ds.Tables.Add(dt)
    '        dr.Close()

    '        If Not Rp Is Nothing Then
    '            If Rp.Errores Then
    '                erro = True
    '            End If
    '        End If
    '    Catch ex As Exception
    '        erro = True
    '        Try
    '            Rp.TieneDatos = False
    '            Rp.Errores = True
    '            Rp.MensajeError = ex.Message
    '            dr.Close()
    '        Catch exinterno As Exception

    '        End Try


    '    End Try

    'End Function

    Public Sub ExecuteNonQuery(ByRef Rp As DotNetResponse.SQLPersistence, ByVal Csql As String)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If


            If MyProvider = Provider.SQLOLEDB Then
                OledbHelper.ExecuteNonquery(Rp, DirectCast(Transa, OleDbTransaction), Csql)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                SqlClientHelper.ExecuteNonquery(Rp, DirectCast(Transa, SqlTransaction), Csql)
            End If

            If Not Rp Is Nothing Then
                If Rp.Errores Then
                    erro = True
                End If
            End If
        Catch ex As Exception
            'GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try


        End Try

    End Sub
    Public Sub ExecuteNonquery(ByRef Rp As DotNetResponse.SQLPersistence)

        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                OledbHelper.ExecuteNonquery(DirectCast(Transa, OleDbTransaction), Rp.StoredProcedure, False, Rp.Parametro)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                SqlClientHelper.ExecuteNonquery(DirectCast(Transa, SqlTransaction), Rp.StoredProcedure, False, Rp.Parametro)
            End If

            If Not Rp Is Nothing Then
                If Rp.Errores Then
                    erro = True
                End If
            End If

        Catch ex As Exception
            'GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try
    End Sub

    Public Sub ExecuteScalar(ByRef Rp As DotNetResponse.SQLPersistence, ByVal Csql As String)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                OledbHelper.ExecuteScalar(DirectCast(Transa, OleDbTransaction), Csql)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                SqlClientHelper.ExecuteScalar(Rp, DirectCast(Transa, SqlTransaction), Csql)
            End If

            'End If

            If Not Rp Is Nothing Then
                If Rp.GetScalar Is Nothing Then
                    Rp.TieneDatos = False
                Else
                    Rp.TieneDatos = True
                End If

                If Rp.Errores Then
                    erro = True
                End If
            End If


        Catch ex As Exception
            '  GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try

    End Sub
    Public Sub ExecuteScalar(ByRef Rp As DotNetResponse.SQLPersistence)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                Rp.GetScalar = OledbHelper.ExecuteScalar(DirectCast(Transa, OleDbTransaction), Rp.StoredProcedure, Rp.Parametro)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                Rp.GetScalar = SqlClientHelper.ExecuteScalar(DirectCast(Transa, SqlTransaction), Rp.StoredProcedure, Rp.Parametro)
            End If
            If Rp.GetScalar Is Nothing Then
                Rp.TieneDatos = False
            End If
            If Not Rp Is Nothing Then
                If Rp.Errores Then
                    erro = True
                End If
            End If

        Catch ex As Exception
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try
    End Sub

    Public Sub ExecuteDataTable(ByRef Rp As DotNetResponse.SQLPersistence, ByVal Csql As String)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                Rp.DtTable = OledbHelper.ExecuteDataset(DirectCast(Transa, OleDbTransaction), Csql).Tables(0)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                Rp.DtTable = SqlClientHelper.ExecuteDataset(DirectCast(Transa, SqlTransaction), Csql).Tables(0)
            End If

            If Not Rp Is Nothing Then
                If Rp.Errores Then
                    erro = True
                End If
            End If

        Catch ex As Exception
            'GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try
    End Sub
    Public Sub ExecuteDataTable(ByRef Rp As DotNetResponse.SQLPersistence)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                Rp.DtTable = OledbHelper.ExecuteDataset(DirectCast(Transa, OleDbTransaction), Rp.StoredProcedure, Rp.Parametro).Tables(0)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                Rp.DtTable = SqlClientHelper.ExecuteDataset(DirectCast(Transa, SqlTransaction), Rp.StoredProcedure, Rp.Parametro).Tables(0)
            End If

            If Not Rp Is Nothing Then
                If Rp.Errores Then
                    erro = True
                End If
            End If

        Catch ex As Exception
            'GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try
    End Sub
    Public Sub ExecuteDataset(ByRef Rp As DotNetResponse.SQLPersistence, ByVal Csql As String)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                Rp.Ds = OledbHelper.ExecuteDataset(DirectCast(Transa, OleDbTransaction), Csql)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                Rp.Ds = SqlClientHelper.ExecuteDataset(DirectCast(Transa, SqlTransaction), Csql)
            End If

            If Not Rp Is Nothing Then
                If Rp.Errores Then
                    erro = True
                End If
            End If
        Catch ex As Exception
            'GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try
    End Sub
    Public Sub ExecuteDataset(ByRef Rp As DotNetResponse.SQLPersistence)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                Rp.Ds = OledbHelper.ExecuteDataset(DirectCast(Transa, OleDbTransaction), Rp.StoredProcedure, Rp.Parametro)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                Rp.Ds = SqlClientHelper.ExecuteDataset(DirectCast(Transa, SqlTransaction), Rp.StoredProcedure, Rp.Parametro)
            End If
            If Not Rp Is Nothing Then
                If Rp.Errores Then
                    erro = True
                End If
            End If

        Catch ex As Exception
            'GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try
    End Sub
    Public Sub ExecuteReader(ByRef Rp As DotNetResponse.SQLPersistence, ByVal Csql As String)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                Rp.GetDataReader = OledbHelper.ExecuteReader(DirectCast(Transa, OleDbTransaction), Csql)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                Rp.GetDataReader = SqlClientHelper.ExecuteReader(DirectCast(Transa, SqlTransaction), Csql)
            End If
            If Not Rp Is Nothing Then
                Rp.TieneDatos = True
                If Rp.Errores Then
                    erro = True
                End If
            End If

        Catch ex As Exception
            'GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try
    End Sub
    Public Sub ExecuteReader(ByRef Rp As DotNetResponse.SQLPersistence)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                Rp.GetDataReader = OledbHelper.ExecuteReader(DirectCast(Transa, OleDbTransaction), Rp.StoredProcedure, Rp.Parametro)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                Rp.GetDataReader = SqlClientHelper.ExecuteReader(DirectCast(Transa, SqlTransaction), Rp.StoredProcedure, Rp.Parametro)
            End If

            If Not Rp Is Nothing Then
                Rp.TieneDatos = True
                If Rp.Errores Then
                    erro = True
                End If
            End If


        Catch ex As Exception
            'GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try
    End Sub
    Public Sub Rollback()
        Try

            Transa.Rollback()
            Close()
        Catch ex As Exception
            Close()
        End Try

    End Sub
    Private Sub Commited()
        If erro = True Then
            Try
                Transa.Rollback()
                Close()
            Catch ex As Exception
            End Try
            Exit Sub
        End If
        Transa.Commit()
    End Sub
    Public Sub Commit()
        If erro = True Then
            Try
                Transa.Rollback()
                Close()
            Catch ex As Exception
            End Try
            Exit Sub
        End If
        If Not cn.State = ConnectionState.Open Then
            Exit Sub
        End If


        If _Comited Then
            Transa.Commit()
        End If

    End Sub
    Public Sub Close()
        cn.Close()
    End Sub
    Private disposedValue As Boolean = False  ' To detect redundant calls
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                'TODO:              free unmanaged resources when explicitly called
            End If

            'TODO: free shared unmanaged resources
        End If
        Me.disposedValue = True
    End Sub


#Region " IDisposable Support "
    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Close()
        Dispose(True)
        GC.SuppressFinalize(Me)
        GC.Collect()
    End Sub
#End Region

End Class
